# -*- coding: utf-8 -*-
'''
Created on Oct 14, 2014

@author: TRAM ANH
'''
import os
import sys
from urlparse import urljoin
sys.path.append(os.path.join(os.path.dirname(__file__), '../'))
import lib
import sqlite3
import time

class Crawler():
    
    databasePath = "/longhoanggiang/database/englishquiz"
    connection = None
    
    def __init__(self):
        self.openAndCreateDatabase()
    
    def __del__(self):
        self.closeDatabase()
    
    def ensureUtf8(self, inputStr):
        if not type(inputStr).__name__ == 'unicode' and len(inputStr) > 0:
            return inputStr.decode('utf-8')
        return inputStr
    
    def closeDatabase(self):
        self.connection.commit()
        self.connection.close()
        
    def addCategory(self, name, parentId, url=''):
        name = self.ensureUtf8(name)
        url = self.ensureUtf8(url)
        c = self.connection.cursor()
        c.execute("INSERT INTO `ta_categories`('name', 'parentId', 'url') VALUES(?, ?, ?)", [name, parentId, url])
        catId = c.lastrowid
        c.close()
        return catId
    
    def addQuestion(self, quizId, question):
        question = self.ensureUtf8(question)
        c = self.connection.cursor()
        c.execute("INSERT INTO `ta_question`('quizId', 'question') VALUES(?, ?)", [quizId, question])
        questionId = c.lastrowid
        c.close()
        return questionId
    
    def addAnswer(self, questionId, answer, isCorrect):
        answer = self.ensureUtf8(answer)
        c = self.connection.cursor()
        c.execute("INSERT INTO `ta_answer`('questionId', 'answer', 'isCorrect') VALUES(?, ?, ?)", [questionId, answer, isCorrect])
        c.close()
    
    def addQuiz(self, catId, name, url):
        name = self.ensureUtf8(name)
        url = self.ensureUtf8(url)
        c = self.connection.cursor()
        c.execute("INSERT INTO `ta_quiz`('name', 'catId', 'url') VALUES(?, ?, ?)", [name, catId, url])
        c.close()
        
    def getQuiz(self):
        c = self.connection.cursor()
        c.execute("SELECT * FROM `ta_quiz`")
        rows = c.fetchall()
        return rows
    
    def openAndCreateDatabase(self):
#         if os.path.exists(self.databasePath):
#             self.databasePath = "{0}-{1}".format(self.databasePath, time.time())
        self.connection = sqlite3.connect(self.databasePath)
        c = self.connection.cursor()
        c.execute("CREATE TABLE IF NOT EXISTS `ta_categories`('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'name' VARCHAR(200), 'parentId' INTEGER DEFAULT 0, 'url' VARCHAR(255))")
        c.execute("CREATE TABLE IF NOT EXISTS `ta_quiz`('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'name' VARCHAR(200), 'catId' INTEGER, 'is_active' INTEGER DEFAULT 1, 'url' VARCHAR(255))")
        c.execute("CREATE TABLE IF NOT EXISTS `ta_question`('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'quizId' INTEGER, 'question' TEXT)")
        c.execute("CREATE TABLE IF NOT EXISTS `ta_answer`('id' INTEGER PRIMARY KEY AUTOINCREMENT, 'questionId' INTEGER, 'answer' TEXT, 'isCorrect' INTEGER DEFAULT 0)")
        c.execute("CREATE INDEX IF NOT EXISTS `ta_answer_question_index` ON `ta_answer`('questionId')")
        c.execute("CREATE INDEX IF NOT EXISTS `ta_question_quiz_index` ON `ta_question`('quizId')")
        c.close()
        
    def fetchQuiz(self):
        quizList = self.getQuiz()
        for quizItem in quizList:
            quizId = quizItem[0]
            quizUrl = quizItem[4]
            print quizId, quizUrl
            tree = lib.Web.load(quizUrl, cached=True, cookie=COOKIESTR).build_tree()
            
            for node in tree.xpath("//div[@align='center']/table/tr/td[3]/form/div[contains(@id, 'question_')]"):
                question = lib.stringify(node)
                questionId = self.addQuestion(quizId, question)
                print question
                for liNode in node.xpath("./following-sibling::ul[1]/li"):
                    answer = lib.stringify(liNode)
                    isCorrect = int(float(liNode.xpath(".//input[@type='hidden']")[0].get('value')))
                    self.addAnswer(questionId, answer, isCorrect)
                    print answer, isCorrect
    
    def getListQuiz(self, catItem):
        catId = catItem['catId']
        url = catItem['url']
        quizList = []
        while url != '':
            tree = lib.Web.load(url, cached=True, cookie=COOKIESTR).build_tree()
            for node in tree.xpath("//div[@align='center']/table/tr/td[3]/a[@class='m']"):
                name = lib.stringify(node)
                link = urljoin(url, node.get('href'))
                quizList.append({'catId': catId, 'name': name, 'link': link})
                print name, link
                
            nextNode = tree.xpath("//div[@class='navigation']//span/a[contains(., '»')]".decode('utf-8'))
            if len(nextNode) == 0:
                break
            else:
                url = urljoin(url, nextNode[0].get('href'))
        quizList.reverse()
        for quiz in quizList:
            self.addQuiz(quiz['catId'], quiz['name'], quiz['link'])
    
    def process(self):
        url = "http://tienganhabc.com/index.php?cat=7"
        tree = lib.Web.load(url, cached=True, cookie=COOKIESTR).build_tree()
        tdNodes = tree.xpath("//div[@align='center']/table/tr/td[3]/h1")
        linkJobs = []
        for node in tdNodes:
            categoryName = lib.stringify(node)
            catId = self.addCategory(categoryName, 0)
            for childNode in node.xpath("./following-sibling::*"):
                if childNode.tag != 'a': break
                categoryName = lib.stringify(childNode)
                link = urljoin(url, childNode.get('href'))
                subCatId = self.addCategory(categoryName, catId, link)
                linkJobs.append({'catId': subCatId, 'url': link})
        for cat in linkJobs:
            self.getListQuiz(cat)
        #Lay list cau hoi, tra loi
        c.fetchQuiz()
        
        
if __name__ == '__main__':
    
    COOKIESTR = "HIM_on_off=0; HIM_method=0; HIM_ckspell=1; HIM_daucu=1; HstCfa1239677=1413297114762; HstCla1239677=1413390510782; HstCmu1239677=1413297114762; HstPn1239677=44; HstPt1239677=64; HstCnv1239677=2; HstCns1239677=4; c_ref_1239677=http%3A%2F%2F6.vndic.net%2Findex.php%3Fid%3D%2Fabc%2Fa%2Fa%255E%2C1%2C0%2C0.html; PHPSESSID=n1i7aqbnl6l6pd1a63qd4to7o5"
    c = Crawler()
    c.process()
#     c.fetchQuiz()
    c.closeDatabase()
    print '> Finished at {0}'.format(time.time())
    os._exit(1)
    